﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class DOCGIADAO
    {

        public DataTable Loaddocgia(string madocgia)
        {
            DataTable datatable = new DataTable();
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM DOC_GIA WHERE MadocGia = '" + madocgia + "'";
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(sql, conn);
            sqldataadapter.Fill(datatable);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();
            return datatable;
        }

        public int update(string strquery, DataTable datatable)
        {
            int effrow = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strquery;
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            cmd.Connection = conn;
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlcomandbuilder = new SqlCommandBuilder(sqldataadapter);
            effrow = sqldataadapter.Update(datatable);
            sqldataadapter.Dispose();
            conn.Close();
            conn.Dispose();
            return effrow;
        }
        public int themdocgia(DOCGIADTO dgDTO)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "INSERT INTO DOC_GIA VALUES(@MDG,@HT,@NS,@DT,@DC,@E,@TN,@LDG,@NLT,@NHH,@MNV,@TT)";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlParameter para = new SqlParameter("@MDG", System.Data.SqlDbType.NChar, 10);
            para.Value = dgDTO.Madocgia;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@HT", System.Data.SqlDbType.NVarChar, 50);
            para.Value = dgDTO.Hoten;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NS", System.Data.SqlDbType.DateTime);
            para.Value = dgDTO.Ngaysinh;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@DT", System.Data.SqlDbType.NChar, 11);
            para.Value = dgDTO.Dienthoai;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@DC", System.Data.SqlDbType.NVarChar, 100);
            para.Value = dgDTO.Diachi;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@E", System.Data.SqlDbType.NChar, 50);
            para.Value = dgDTO.Email;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@TN", System.Data.SqlDbType.Money);
            para.Value = dgDTO.Tienno;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@LDG", System.Data.SqlDbType.NChar, 3);
            para.Value = dgDTO.Maloaidocgia;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NLT", System.Data.SqlDbType.DateTime);
            para.Value = dgDTO.Ngaylapthe;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NHH", System.Data.SqlDbType.DateTime);
            para.Value = dgDTO.Ngayhethan;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@MNV", System.Data.SqlDbType.NChar, 6);
            para.Value = dgDTO.Manhanvien;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@TT", System.Data.SqlDbType.NChar, 3);
            para.Value = dgDTO.Matinhtrang;
            cmd.Parameters.Add(para);

            int kq = cmd.ExecuteNonQuery();
            if (kq > 0)
            {
                BIENDOCGIADAO dgDAO = new BIENDOCGIADAO();
                dgDAO.updatebiendocgia(dgDTO);
            }
            conn.Close();
            conn.Dispose();
            return kq;
        }

        public string MaDocGia(string maloaidocgia)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT GiaTri FROM BIEN_DOC_GIA WHERE TenBien='" + maloaidocgia + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            object kq2 = cmd.ExecuteScalar();
            int kq = Convert.ToInt32(kq2);
            kq++;
            string kq1 = kq.ToString();
            string temp = "0";
            while (kq1.Length != 3)
                kq1 = temp + kq1;
            maloaidocgia += kq1;
            conn.Close();
            conn.Dispose();
            return maloaidocgia;
        }
        public DataTable Loaddocgia()
        {
            DataTable datatble = new DataTable();
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM DOC_GIA";
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(sql, conn);
            sqldataadapter.Fill(datatble);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();
            return datatble;
        }
    }
}
